package com.bnz.dao;

import com.bnz.entity.Student;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;



/**
 * @author xy
 * @date 2022/10/25
 */
@Repository
public interface StudentDao extends JpaRepository<Student,Integer> {

    @Query(value = "select  sid , sname , sex ,age ,addr ,sc.cid ,cname " +
            "from student_copy1 sc left join classes c on sc.cid = c.cid  " +
            "where 1=1 " +
            "and if (:sname !='',sc.sname like concat('%',:sname,'%'),1=1 ) " +
            "and if (:addr !='', sc.addr like concat('%',:addr,'%'),1=1 ) " +
            "and if (:cid !='',sc.cid = :cid ,1=1)",
            countQuery = "select count(sid) from student_copy1",
            nativeQuery = true)
    Page<Student> findDemo(@Param("sname")String sname, @Param("addr") String addr ,@Param("cid") Integer cid, Pageable pageable);

}
